-- @owner: lonely-dance
-- @date: 2022-03-30
-- @testpoint: 建表(select)

--step1:建表;expect:成功
drop table if exists t_select_001 cascade;
create table t_select_001
(
id                                  bytea,
class_Name                          VARCHAR(60 ),
class_Id                            BIGINT,
last_Modified                       BIGINT,
collectorId                                  VARCHAR(36 ),
reportSn                                     BIGINT,
regionId                                     bytea,
createTime                                   BIGINT,
name                                VARCHAR(255 ),
remark                                       VARCHAR(1024 ),
nativeId                                     VARCHAR(255 ),
ownerId                                      BIGINT,
bandwidth                                    float8,
frameId                                      VARCHAR(32 ),
portId                                       VARCHAR(32 ),
cardNativeId                                 VARCHAR(255 ),
portIndex                                    VARCHAR(255 ),
ltpTypeName                         VARCHAR(128 ),
adminState                                   VARCHAR(32 ),
layerRate                                    VARCHAR(64 ),
alias                                        VARCHAR(255 ),
slotId                                       VARCHAR(32 ),
daughterSlotId                               VARCHAR(32 ),
neNativeId                                   VARCHAR(255 ),
direction                                    VARCHAR(32 ),
Trunk__trunkId                               VARCHAR(32 ),
userLabel                                    VARCHAR(256 ),
FanVoipAG__mgId                              INTEGER,
workMode                                     VARCHAR(64 ),
OTN__flexFreq                                VARCHAR(32 ),
MPLSTE__availableBandwidth                   float8,
Eth__vlanType                                VARCHAR(32 ),
MPLSTE__maxReservedBandwidth                 float8,
OTN__workBand                                VARCHAR(32 ),
Eth__mtu                                     INTEGER,
Optical__opticalModule                       VARCHAR(32 ),
ltpRole                                      VARCHAR(32 ),
isSubLTP                                     BOOLEAN,
owner                                        VARCHAR(32 ),
IP__addrv4Mask                               INTEGER,
VNI__vniId                                   INTEGER,
isPhysical                                   BOOLEAN,
IP__unnumberedNativeId                       VARCHAR(255 ),
NVE__sourceIP                                VARCHAR(64 ),
OTN__workBandParity                          VARCHAR(32 ),
capabilityURI                                VARCHAR(128 ),
OTN__waveNo                                  VARCHAR(32 ),
Physical__mediumType                         VARCHAR(32 ),
Eth__vlan                                    CLOB,
FanVoipUser__telNumber                       VARCHAR(32 ),
Eth__tunnelId                                INTEGER,
tags                                         VARCHAR(2048 ),
SDH__highPath                                INTEGER,
SDH__lowPath                                 INTEGER,
portType                                     INTEGER,
C64K__timeslot                               VARCHAR(100 ),
IP__addrv6                                   CLOB,
Eth__mac                                     VARCHAR(64 ),
FanVLAN__vlanId                              INTEGER,
OTN__pathId                                  VARCHAR(32 ),
OTN__freqWidth                               VARCHAR(32 ),
IP__addrv4                                   VARCHAR(32 )
) WITH (STORAGE_TYPE=USTORE);

--step2:建表;expect:成功
DROP TABLE IF EXISTS t_select_002 cascade;
CREATE TABLE  t_select_002(
     COL_1 integer,
     COL_2 bigint,
     COL_3 float8,
     COL_4 decimal(12,6),
     COL_5 bool,
     COL_6 char(30),
     COL_7 varchar2(50),
     COL_8 varchar(30),
     COL_9 interval day to second,
     COL_10 TIMESTAMP,
     COL_11 date,
     COL_12 date,
     COL_13 TIMESTAMP WITHOUT TIME ZONE,
     COL_14 blob,
     COL_15 clob,
     COL_16 int[]
) WITH (STORAGE_TYPE=USTORE);

--step3:建序列;expect:成功
drop sequence if exists s_select_001;
create sequence s_select_001 increment by 1 start with 10;

--step4:清空数据;expect:成功
truncate table t_select_002;

--step5:插入数据;expect:成功
begin
 for i in 1..50 loop
      insert into t_select_002 values(
   i,
      s_select_001.nextval,
   i+445.255,
   98*0.99*i,
   true,
   lpad('abc','30','@'),
   lpad('abc','30','b'),
   rpad('abc','30','e'),
   (INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),
   to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
   '2018-11-03 14:14:12',
   '2019-01-03 14:14:12',
   '2019-01-03 14:14:12',
   rawtohex(lpad('10','12','01010'))::blob,
   rpad('abc','9','a@123&^%djgk')::clob,
   '{32,535,5645645,6767,76,67,56,48,979,978,7}'
   );
    end loop;
end;
/